﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using PPI.Data;
using PPI.Data.Distribution;
using PPI.Entities.Interface;
using PPI.Mobile;
using PPIDataAccess.Distribution;

namespace PPI.ServiceLogic.Distribution
{
    public class LPole : IMobileTable
    {
        public List<EMobileQuery> CreateInsertMobileQuery(string connectionKey)
        {
            List<EMobileQuery> lstQueries = new List<EMobileQuery>();
            
            lstQueries.AddRange(GetPoleQuery(connectionKey));
            lstQueries.AddRange(GetPoleNotes(connectionKey));
            lstQueries.AddRange(GetImagePoleQuery(connectionKey));
            lstQueries.AddRange(GetMeterQuery(connectionKey));
            lstQueries.AddRange(GetMeterNotesQuery(connectionKey));
            lstQueries.AddRange(GetInspectionResultsQuery(connectionKey));
            lstQueries.AddRange(GetPhasesQuery(connectionKey));
            lstQueries.AddRange(GetTopsQuery(connectionKey));

            return lstQueries;
        }

        public List<EMobileQuery> GetPoleQuery(string connectionKey)
        {
            DataTable poles = DPole.Select(connectionKey).Tables[0];
            List<EMobileQuery> listQueries = new List<EMobileQuery>();
            listQueries.Add(new EMobileQuery("DELETE tb_Poles;"));

            StringBuilder query = new StringBuilder();
            query.AppendLine("INSERT INTO tb_Poles ");
            query.AppendLine("(PoleId, PolygonMapFk, PolygonSectionFk, SpecieFk,");
            query.AppendLine("ClassFk, LengthFk, TreatmentFk, ");
            query.AppendLine("Latitude, Longitude, InspBy, ");
            query.AppendLine("Number, AcquisitionDate, Year,");
            query.AppendLine("LVisit, D804, DistributionType, VendorFK, InspectorFK, ProjectFK, GuardsQuantity, PoleTag, GroundWireRepair, Billable) VALUES (");
            query.AppendLine("@PoleId, @PolygonMapFk, @PolygonSectionFk, @SpecieFk, ");
            query.AppendLine("@ClassFk, @LengthFk, @TreatmentFk, ");
            query.AppendLine("@Latitude, @Longitude, @InspBy, ");
            query.AppendLine("@Number, @AcquisitionDate, @Year, ");
            query.AppendLine("@LVisit, @D804, @DistributionType, @VendorFK, @InspectorFK, @ProjectFK, @GuardsQuantity, @PoleTag, @GroundWireRepair, @Billable); ");

            EMobileQuery insertQuery = new EMobileQuery(query.ToString());

            foreach (DataRow pole in poles.Rows)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();

                if (pole["Vendor_Distribution_Poles_PoleId"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, Convert.ToInt32(pole["Vendor_Distribution_Poles_PoleId"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, DBNull.Value));
                
                if (pole["MapFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, Convert.ToInt32(pole["MapFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole["SectionFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, Convert.ToInt32(pole["SectionFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole["SpecieFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, Convert.ToInt32(pole["SpecieFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole["ClassFK"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, Convert.ToInt32(pole["ClassFK"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole["LengthFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, Convert.ToInt32(pole["LengthFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole["TreatmentFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, Convert.ToInt32(pole["TreatmentFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole["Latitude"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, Convert.ToDouble(pole["Latitude"])));
                else
                    parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, DBNull.Value));

                if (pole["Longitude"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, Convert.ToDouble(pole["Longitude"])));
                else
                    parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, DBNull.Value));

                if (pole["InspBy"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, pole["InspBy"]));
                else
                    parameters.Add(DBHelper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole["Number"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, pole["Number"]));
                else
                    parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole["AcquisitionDate"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, Convert.ToDateTime(pole["AcquisitionDate"])));
                else
                    parameters.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, DBNull.Value));

                if (pole["Year"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, Convert.ToInt32(pole["Year"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, DBNull.Value));

                if (pole["LVisit"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, Convert.ToDateTime(pole["LVisit"])));
                else
                    parameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, DBNull.Value));

                if (pole["D804"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateNVarCharParameter("@D804", 100, ParameterDirection.Input, false, Convert.ToString(pole["D804"])));
                else
                    parameters.Add(DBHelper.CreateNVarCharParameter("@D804", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole["DistributionType"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@DistributionType", ParameterDirection.Input, false, Convert.ToInt32(pole["DistributionType"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@DistributionType", ParameterDirection.Input, false, DBNull.Value));

                if (pole["VendorFK"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@VendorFK", ParameterDirection.Input, false, Convert.ToInt32(pole["VendorFK"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@VendorFK", ParameterDirection.Input, false, DBNull.Value));

                if (pole["InspectorFK"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@InspectorFK", ParameterDirection.Input, false, Convert.ToInt32(pole["InspectorFK"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@InspectorFK", ParameterDirection.Input, false, DBNull.Value));

                if (pole["ProjectFK"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@ProjectFK", ParameterDirection.Input, false, Convert.ToInt32(pole["ProjectFK"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@ProjectFK", ParameterDirection.Input, false, DBNull.Value));

                if (pole["GuardsQuantity"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, Convert.ToInt32(pole["GuardsQuantity"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, DBNull.Value));

                if (pole["PoleTag"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateBitParameter("@PoleTag", ParameterDirection.Input, false, Convert.ToBoolean(pole["PoleTag"])));
                else
                    parameters.Add(DBHelper.CreateBitParameter("@PoleTag", ParameterDirection.Input, false, DBNull.Value));

                if (pole["GroundWireRepair"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, Convert.ToBoolean(pole["GroundWireRepair"])));
                else
                    parameters.Add(DBHelper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, DBNull.Value));

                if (pole["Billable"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateBitParameter("@Billable", ParameterDirection.Input, false, Convert.ToBoolean(pole["Billable"])));
                else
                    parameters.Add(DBHelper.CreateBitParameter("@Billable", ParameterDirection.Input, false, DBNull.Value));

                insertQuery.Parameters.Add(parameters);
            }
            
            if (insertQuery.Parameters.Count > 0)
                listQueries.Add(insertQuery);
            
            return listQueries;
        }

        public List<EMobileQuery> GetPoleNotes(string connectionKey)
        {
            DataTable images = DNote.SelectPoleNotes(connectionKey).Tables[0];

            List<EMobileQuery> listQueries = new List<EMobileQuery>();
            listQueries.Add(new EMobileQuery("DELETE tb_PoleResults;"));

            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO tb_PoleResults ");
            query.AppendLine("(PoleResult, PoleFk, ResultTypeFk) ");
            query.AppendLine("VALUES (@PoleResult, @PoleFk, @ResultTypeFk )");

            EMobileQuery insertQuery = new EMobileQuery(query.ToString());

            foreach (DataRow image in images.Rows)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();

                if (image["Vendor_Distribution_Results_ResultId"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PoleResult", ParameterDirection.Input, false, Convert.ToInt32(image["Vendor_Distribution_Results_ResultId"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PoleResult", ParameterDirection.Input, false, DBNull.Value));

                if (image["Vendor_Distribution_PoleFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, Convert.ToInt32(image["Vendor_Distribution_PoleFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, DBNull.Value));

                if (image["Vendor_Distribution_ResultTypeFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, Convert.ToInt32(image["Vendor_Distribution_ResultTypeFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, DBNull.Value));

                insertQuery.Parameters.Add(parameters);
            }

            if (insertQuery.Parameters.Count > 0)
                listQueries.Add(insertQuery);

            return listQueries;
        }

        public List<EMobileQuery> GetImagePoleQuery(string connectionKey)
        {
            DataTable images = DImages.SelectPoleImages(connectionKey).Tables[0];

            List<EMobileQuery> listQueries = new List<EMobileQuery>();
            listQueries.Add(new EMobileQuery("DELETE tb_PoleImages;"));

            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO tb_PoleImages ");
            query.AppendLine("(ImageId, PoleFk, FileName) ");
            query.AppendLine("VALUES (@ImageId, @PoleFk, @FileName )");

            EMobileQuery insertQuery = new EMobileQuery(query.ToString());

            foreach (DataRow image in images.Rows)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();

                if (image["tb_Vendor_Distribution_Images_ImageId"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.Input, false, Convert.ToInt32(image["tb_Vendor_Distribution_Images_ImageId"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.Input, false, DBNull.Value));

                if (image["Vendor_Distribution_PoleFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, Convert.ToInt32(image["Vendor_Distribution_PoleFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, DBNull.Value));

                if (image["filename"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateNVarCharParameter("@FileName", 100, ParameterDirection.Input, false, image["filename"]));
                else
                    parameters.Add(DBHelper.CreateNVarCharParameter("@FileName", 100, ParameterDirection.Input, false, DBNull.Value));

                insertQuery.Parameters.Add(parameters);
            }
            
            if (insertQuery.Parameters.Count > 0)
                listQueries.Add(insertQuery);

            return listQueries;
        }

        public List<EMobileQuery> GetMeterQuery(string connectionKey)
        {
            List<EMobileQuery> listQueries = new List<EMobileQuery>();
            DataTable meters = DMeter.Select(connectionKey).Tables[0];

            listQueries.Add(new EMobileQuery("DELETE tb_Meters;"));

            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO tb_Meters ");
            query.AppendLine("(MeterId, Latitude, Longitude, PoleFk, Number, ImageFileName, Comments) ");
            query.AppendLine("VALUES (@MeterId, @Latitude, @Longitude, @PoleFk, @Number, @ImageFileName, @Comments)");

            EMobileQuery insertQuery = new EMobileQuery(query.ToString());

            foreach (DataRow meter in meters.Rows)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();

                if (meter["Vendor_Distribution_Meters_MeterId"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, Convert.ToInt32(meter["Vendor_Distribution_Meters_MeterId"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, DBNull.Value));

                if (meter["Vendor_Distribution_PoleFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, Convert.ToInt32(meter["Vendor_Distribution_PoleFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, DBNull.Value));

                if (meter["ImageFileName"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateNVarCharParameter("@ImageFileName", 100, ParameterDirection.Input, false, meter["ImageFileName"]));
                else
                    parameters.Add(DBHelper.CreateNVarCharParameter("@ImageFileName", 100, ParameterDirection.Input, false,DBNull.Value));

                if (meter["Number"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false,meter["Number"]));
                else
                    parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, DBNull.Value));
                
                if (meter["Comments"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false,meter["Comments"]));
                else
                    parameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, DBNull.Value));

                if (meter["Latitude"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false,Convert.ToDouble(meter["Latitude"])));
                else
                    parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, DBNull.Value));
                
                if (meter["Longitude"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false,Convert.ToDouble(meter["Longitude"])));
                else
                    parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, DBNull.Value));

                insertQuery.Parameters.Add(parameters);
            }

            if (insertQuery.Parameters.Count > 0)
                listQueries.Add(insertQuery);            

            return listQueries;
        }

        public List<EMobileQuery> GetMeterNotesQuery(string connectionKey)
        {
            List<EMobileQuery> listQueries = new List<EMobileQuery>();

            DataTable meterNotes = DMeterNote.Select(connectionKey).Tables[0];

            listQueries.Add(new EMobileQuery("DELETE tb_NotesXMeters;"));

            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO tb_NotesXMeters ");
            query.AppendLine("(NoteXMeterId, NoteFk, MeterFk) ");
            query.AppendLine("VALUES (@NoteXMeterId, @NoteFk, @MeterFk)");

            EMobileQuery insertQuery = new EMobileQuery(query.ToString());

            foreach (DataRow meterNote in meterNotes.Rows)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();

                if (meterNote["Vendor_Distribution_NotesXMeter_NoteXMeterId"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@NoteXMeterId", ParameterDirection.Input, false, Convert.ToInt32(meterNote["Vendor_Distribution_NotesXMeter_NoteXMeterId"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@NoteXMeterId", ParameterDirection.Input, false, DBNull.Value));

                if (meterNote["Vendor_Distribution_MeterFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@MeterFk", ParameterDirection.Input, false, Convert.ToInt32(meterNote["Vendor_Distribution_MeterFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@MeterFk", ParameterDirection.Input, false, DBNull.Value));

                if (meterNote["Vendor_Distribution_NoteFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, Convert.ToInt32(meterNote["Vendor_Distribution_NoteFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, DBNull.Value));

                insertQuery.Parameters.Add(parameters);
            }

            if (insertQuery.Parameters.Count > 0)
                listQueries.Add(insertQuery);

            return listQueries;
        }

        public List<EMobileQuery> GetInspectionResultsQuery(string connectionKey)
        {
            List<EMobileQuery> listQueries = new List<EMobileQuery>();
            DataTable inspetionResult = DInspectionResult.Select(connectionKey).Tables[0];

            listQueries.Add(new EMobileQuery("DELETE tb_Pole_InspectionResult;"));

            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO tb_Pole_InspectionResult ");
            query.AppendLine("(PoleInspectionResultId, InspectionTypeFk, Comments, isElectrical, PoleFk, ResultTypeFk) ");
            query.AppendLine("VALUES (@PoleInspectionResultId, @InspectionTypeFk, @Comments, @isElectrical, @PoleFk, @ResultTypeFk)");

            EMobileQuery insertQuery = new EMobileQuery(query.ToString());

            foreach (DataRow inspection in inspetionResult.Rows)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();

                if (inspection["Vendor_Distribution_Results_ResultId"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PoleInspectionResultId", ParameterDirection.Input, false, Convert.ToInt32(inspection["Vendor_Distribution_Results_ResultId"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PoleInspectionResultId", ParameterDirection.Input, false, DBNull.Value));

                if (inspection["Vendor_Distribution_TypeFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, Convert.ToInt32(inspection["Vendor_Distribution_TypeFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, DBNull.Value));

                if (inspection["Vendor_Distribution_PoleFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, Convert.ToInt32(inspection["Vendor_Distribution_PoleFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, DBNull.Value));

                if (inspection["Vendor_Distribution_ResultTypeFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, Convert.ToInt32(inspection["Vendor_Distribution_ResultTypeFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, DBNull.Value));

                if (inspection["Comments"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, inspection["Comments"]));
                else
                    parameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, DBNull.Value));

                if (inspection["bElectrical"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateBitParameter("@isElectrical", ParameterDirection.Input, false, inspection["bElectrical"]));
                else
                    parameters.Add(DBHelper.CreateBitParameter("@isElectrical", ParameterDirection.Input, false, DBNull.Value));

                insertQuery.Parameters.Add(parameters); 
            }

            if (insertQuery.Parameters.Count > 0)
                listQueries.Add(insertQuery);

            return listQueries;
        }

        public List<EMobileQuery> GetPhasesQuery(string connectionKey)
        {
            List<EMobileQuery> listQueries = new List<EMobileQuery>();
            DataTable phases = DPhase.Select(connectionKey).Tables[0];

            listQueries.Add(new EMobileQuery("DELETE tb_PhasesXPoles;"));

            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO tb_PhasesXPoles ");
            query.AppendLine("(PhaseXPoleId, PoleFk, PhaseFk) ");
            query.AppendLine("VALUES (@PhaseXPoleId, @PoleFk, @PhaseFk)");

            EMobileQuery insertQuery = new EMobileQuery(query.ToString());

            foreach (DataRow phase in phases.Rows)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();

                if (phase["Vendor_Distribution_PhasesXPole_PhaseXPoleId"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PhaseXPoleId", ParameterDirection.Input, false, Convert.ToInt32(phase["Vendor_Distribution_PhasesXPole_PhaseXPoleId"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PhaseXPoleId", ParameterDirection.Input, false, DBNull.Value));

                if (phase["Vendor_Distribution_PoleFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, Convert.ToInt32(phase["Vendor_Distribution_PoleFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, DBNull.Value));

                if (phase["Vendor_Distribution_PhaseFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PhaseFk", ParameterDirection.Input, false, Convert.ToInt32(phase["Vendor_Distribution_PhaseFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PhaseFk", ParameterDirection.Input, false, DBNull.Value));
               
                insertQuery.Parameters.Add(parameters);
            }

            if (insertQuery.Parameters.Count > 0)
                listQueries.Add(insertQuery);

            return listQueries;
        }

        public List<EMobileQuery> GetTopsQuery(string connectionKey)
        {
            List<EMobileQuery> listQueries = new List<EMobileQuery>();
            DataTable tops = DTops.Select(connectionKey).Tables[0];

            listQueries.Add(new EMobileQuery("DELETE tb_TopsXPoles;"));

            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO tb_TopsXPoles ");
            query.AppendLine("(TopXPoleId, PoleFk, TopFk) ");
            query.AppendLine("VALUES (@TopXPoleId, @PoleFk, @TopFk)");

            EMobileQuery insertQuery = new EMobileQuery(query.ToString());

            foreach (DataRow top in tops.Rows)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();

                if (top["Vendor_Distribution_TopsXPole_TopXPoleId"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@TopXPoleId", ParameterDirection.Input, false, Convert.ToInt32(top["Vendor_Distribution_TopsXPole_TopXPoleId"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@TopXPoleId", ParameterDirection.Input, false, DBNull.Value));

                if (top["Vendor_Distribution_PoleFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, Convert.ToInt32(top["Vendor_Distribution_PoleFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, DBNull.Value));

                if (top["Vendor_Distribution_TopFk"] != DBNull.Value)
                    parameters.Add(DBHelper.CreateIntParameter("@TopFk", ParameterDirection.Input, false, Convert.ToInt32(top["Vendor_Distribution_TopFk"])));
                else
                    parameters.Add(DBHelper.CreateIntParameter("@TopFk", ParameterDirection.Input, false, DBNull.Value));

                insertQuery.Parameters.Add(parameters);

            }

            if (insertQuery.Parameters.Count > 0)
                listQueries.Add(insertQuery);

            return listQueries;
        }
    }
}
